﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using Runda.EShop.Common.Utility;
using System.Data.SqlClient;

namespace Runda.EShop.Dal
{
    public partial class BizOrderDal
    {
        public DataTable GetOrderList(List<SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} * FROM Biz_Order WHERE Deleted = 0 {1}");
            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable QueryOrderList(List<SqlDbParameter> parms,string ProductName,string CateCode, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
        {
            StringBuilder sqlSb = new StringBuilder(@"SELECT {0} * FROM Biz_Order WHERE Deleted = 0 {1}");

            string WhereClauseFormat = @" and OrderID in (
select OrderID FROM Biz_Order_Detail A JOIN Bas_Product B ON A.ProductID = B.ProductID
WHERE 1=1 ";
            if (! string.IsNullOrEmpty(ProductName))
            {
                WhereClauseFormat += " and B.ProductName LIKE '%"+ProductName+"%'";
            }
            if (!string.IsNullOrEmpty(CateCode))
            {
                WhereClauseFormat += " and B.CateCode LIKE '" + CateCode + "%'";
            }

            WhereClauseFormat += ")";

            if (!string.IsNullOrEmpty(ProductName) || !string.IsNullOrEmpty(CateCode))
            {
                sqlSb.Append(WhereClauseFormat);
            }

            string sqlStr = SqlDbParameter.BuildSqlString(sqlSb.ToString(), parms);

            string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {1} ) AS RowNumber, * 
  FROM ({0}) as A
)
SELECT * from T WHERE RowNumber > @StartRowNum AND  RowNumber <= @EndRowNum
select count(*) from ({0}) as B 
";

            CmdText = string.Format(CmdText, sqlStr, OrderBy);

            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(CmdText);
            CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex) * PageSize);
            CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, (PageIndex + 1) * PageSize);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
            RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

        public DataTable ExportOrderList(List<SqlDbParameter> parms)
        {
            string sql = @"
SELECT {0} * FROM (
select E.EntName,C.CustomerName,C.CustomerID,O.OrderDate,O.OrderCode,O.OrderID,O.OrderType
,Pd.ProductCode,Pd.ProductName,Pd.Alias,Pd.Standard,Pd.Unit ,D.BatchCode,D.DueDate
,D.Quantity,P.AssignQty,P.ActualQty ,pd.Trademark
,O.OrderStatus
from Biz_Order O JOIN Biz_Order_Detail D 
ON O.OrderID = D.OrderID
LEFT JOIN Biz_OrderDetail_Patch P ON D.DetailID=P.DetailID
JOIN Biz_Customer C ON C.CustomerID=O.CustomerID
JOIN Biz_Enterprise E ON C.EntID = E.EntID
JOIN Bas_Product Pd ON D.ProductID = Pd.ProductID
)  T WHERE 1=1 {1}
";
            sql = SqlDbParameter.BuildSqlString(sql, parms);
            DbCommand dbCommand = this.CurrentDatabase.GetSqlStringCommand(sql);
            foreach (SqlDbParameter parameter in parms)
            {
                parameter.SetDbCommond(dbCommand);
            }

            DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand); 
            return ds.Tables[0];
        }

        public void UpdateOrderStatus(Guid OrderID, EnumHelper.OrderStatus orderStatus)
        {
            string sql = "UPDATE Biz_Order SET OrderStatus=@Status WHERE OrderID=@OrderID";

            DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
            CurrentDatabase.AddInParameter(dbcommand, "@OrderID", DbType.Guid, OrderID);
            CurrentDatabase.AddInParameter(dbcommand, "@Status", DbType.Int32, (int)orderStatus);

            if (base.Tran != null)
                CurrentDatabase.ExecuteNonQuery(dbcommand, Tran);
            else
                CurrentDatabase.ExecuteNonQuery(dbcommand);
        }

        public void UpdateOrderEmptyStatus(Guid OrderID,List<Guid> details)
        {
            DataTable table=new DataTable("OrderDetailTableType");
                table.Columns.Add("DetailID", typeof(System.Guid));

                DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("usp_UpdateOrderEmptyStatus");
           
                SqlCommand sqlcommand = dbcommand as SqlCommand;
                sqlcommand.Parameters.AddWithValue( "@OrderID", OrderID);
            sqlcommand.Parameters.AddWithValue("@OrderDetailTableType", table);

            if (base.Tran != null)
                CurrentDatabase.ExecuteNonQuery(sqlcommand, Tran);
            else
                CurrentDatabase.ExecuteNonQuery(sqlcommand);
        }
    }
}
